package db;

import beans.B_Cliente;
import beans.B_Persona;
import beans.B_PlanAuditoria;
import beans.B_PlanAuditoriaSede;
import beans.B_Sede;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import conexion.Conexion;
import java.sql.Types;

/**
 *
 * @author JoseHernan
 */
public class DB_PlanAuditoriaSede {

    public static ArrayList<B_PlanAuditoriaSede> getPlanAuditoriaSede(int id) {
        ArrayList<B_PlanAuditoriaSede> list = new ArrayList<B_PlanAuditoriaSede>();
        try {
            Connection con = Conexion.getConexion();

            PreparedStatement ps;
            ResultSet rs;

            ps = con.prepareStatement(" select id, id_sede, id_plan_auditoria from tbl_plan_auditoria_sede where id = " + id);

            rs = ps.executeQuery();
            B_PlanAuditoriaSede obj;
            B_PlanAuditoria objPlanAuditoria;
            B_Sede objSede;
            while (rs.next()) {
                obj = new B_PlanAuditoriaSede();
                objPlanAuditoria = new B_PlanAuditoria();
                objSede = new B_Sede();
                //Asign values
                obj.setId(rs.getInt("id"));
                objSede.setId(rs.getInt("id_sede"));
                obj.setObjSede(objSede);
                objPlanAuditoria.setId(rs.getInt("id_plan_auditoria"));
                obj.setObjPlanAuditoria(objPlanAuditoria);
                //Add to List
                list.add(obj);
            }
            rs.close();
            ps.close();
            con.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return list;
    }

    public static ArrayList<B_PlanAuditoriaSede> getListByIdPlanAuditoria() {
        ArrayList<B_PlanAuditoriaSede> list = new ArrayList<B_PlanAuditoriaSede>();
        try {
            Connection con = Conexion.getConexion();

            PreparedStatement ps;
            ResultSet rs;

            ps = con.prepareStatement(" select pas.id, s.nombre, s.telefono, s.direccion, c.razon_social, p.apellidos, p.nombres from tbl_plan_auditoria_sede pas inner join tbl_plan_auditoria pa on pa.id = pas.id_plan_auditoria inner join tbl_sede s on s.id = pas.id_sede inner join tbl_cliente c on c.id = s.id_cliente inner join tbl_persona p on p.id = c.id_persona where pa.removed = false and pa.is_active = true and pas.removed = false ");

            rs = ps.executeQuery();
            B_PlanAuditoriaSede obj;
            B_Persona objPersona;
            B_Sede objSede;
            B_Cliente objCliente;
            while (rs.next()) {
                obj = new B_PlanAuditoriaSede();
                objPersona = new B_Persona();
                objSede = new B_Sede();
                objCliente = new B_Cliente();
                //Asign values
                obj.setId(rs.getInt("id"));
                objPersona.setApellidos(rs.getString("apellidos"));
                objPersona.setNombres(rs.getString("nombres"));
                objCliente.setObjPersona(objPersona);
                objCliente.setRazonSocial(rs.getString("razon_social"));
                objSede.setObjCliente(objCliente);
                objSede.setNombre(rs.getString("nombre"));
                objSede.setTelefono(rs.getString("telefono"));
                objSede.setDireccion(rs.getString("direccion"));
                obj.setObjSede(objSede);
                //Add to List
                list.add(obj);
            }
            rs.close();
            ps.close();
            con.close();
        } catch (Exception ex) {
            System.out.println(ex);
        }
        return list;
    }

    //SP CRUD
    public static String crud(B_PlanAuditoriaSede obj, String action) {
        String result = "";
        CallableStatement cs = null;
        Connection con = Conexion.getConexion();
        try {
            cs = con.prepareCall("{call sp_crud_plan_auditoria_sede(?,?,?,?,?,?)}");

            cs.registerOutParameter("p_message", Types.VARCHAR);
            cs.setString("p_action", action);
            cs.setInt("p_id", obj.getId());
            cs.setInt("p_id_sede", obj.getObjSede().getId());
            cs.setInt("p_id_plan_auditoria", obj.getObjPlanAuditoria().getId());
            cs.setString("p_user", obj.getUser());
            cs.executeUpdate();
            result = cs.getString(1);
        } catch (Exception ex) {
            result = ex.getMessage();
        } finally {
            try {
                cs.close();
                con.close();
            } catch (Exception ex) {
                System.out.println(ex);
            }
        }
        return result;
    }
}
